/**
 * 
 */
package com.itinsys.enma.dao.impl;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import com.itinsys.enma.dao.DBTemplate;
import com.itinsys.enma.util.CareersIntTranspObject;
/**
 * @author pferraz
 *
 */
public class UCUDataDAOImpl extends AbstractDataDAO {

	private static Logger logger = Logger.getLogger("dao");
	
	private static final String SUBJECT_NAME="DESCR";
	private static final String CAREER_NAME="CAREER_NAME";
	private static final String SUBJECT_YEAR="CRSE_ATTR_VALUE";
	
	@Value("#{phProperties.dbTableUserOracle}")
	private String dbUser;
	
	/**
	 * 
	 */
	public UCUDataDAOImpl() {
		// TODO Auto-generated constructor stub
	}


	@Override
	public CareersIntTranspObject getCareers() {
		JdbcTemplate jTemplate = new JdbcTemplate(DBTemplate.getOracleDataSource());
		List<Map<String,Object>> lMap;
		logger.info("Consultando las carreras con sus materias de la base de datos oracle de la UCU\n"+jTemplate);	
//		lMap = jTemplate.queryForList("SELECT DISTINCT m.DESCR,ca.DESCR as CAREER_NAME,c.CRSE_ATTR_VALUE FROM "+dbUser+".PS_CRSE_OFFER_3_VW m JOIN "+dbUser+".PS_CRSE_ATTRIBUTES c ON m.CRSE_ID=c.CRSE_ID JOIN "+dbUser+".PS_ACAD_PROG_TB_VW ca ON ca.ACAD_PROG=c.CRSE_ATTR ORDER BY m.DESCR");
		String query="SELECT DISTINCT m.course_title_long as DESCR,ca.DESCR as CAREER_NAME,m.CRSE_ATTR_VALUE FROM "+dbUser+".PS_UCU_MALLA2_VW m JOIN "+dbUser+".PS_ACAD_PROG_TB_VW ca ON ca.ACAD_PROG=m.ACAD_PROG ORDER BY m.course_title_long";
		
		logger.info(query+"\n");
		lMap = jTemplate.queryForList(query);
		logger.info(lMap.get(0));
		return convertToCareerList(lMap,CAREER_NAME,SUBJECT_YEAR,SUBJECT_NAME);
	}




	@Override
	public String getSchoolIdByCareerName(String careerName) {
		JdbcTemplate jTemplate = new JdbcTemplate(DBTemplate.getOracleDataSource());
		Map<String,Object> map;
		logger.info("Pidiendo el schoolId a traves del nombre de carrera\n"+jTemplate);
		String result="";
		try{
			map = jTemplate.queryForMap("SELECT DISTINCT(ACAD_GROUP) as SCHOOL_ID FROM "+dbUser+".PS_ACAD_PROG_TB_VW WHERE DESCR='"+careerName+"'");
			result=map.get("SCHOOL_ID").toString();
		}catch(EmptyResultDataAccessException e){
			logger.debug("No se encontro la facultad por la carrera en UCU");
		}
		return result;
	}

}
